package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

import model.LogInfo;
import model.PageBean;
import net.sf.json.JSONArray;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import util.DateUtil;
import util.IdUtil;
import util.JdbcUtil;
import util.JsonUtil;
import exception.SystemException;

public class LogInfoDao {

	private static final Log log = LogFactory.getLog(LogInfoDao.class);

	public int deleteLogInfo(String owner, String groupName) throws SystemException {
		String sql = "delete from optim.modudf_log m where m.owner = '" + owner
				+ "' and m.group_name='" + groupName + "'";
		log.debug(sql);
		Connection conn = JdbcUtil.getAdminConnection();
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			return pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int addLogInfo(LogInfo logInfo) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = "insert into optim.modudf_log(lid,owner,group_name,oper_name,oper_desc,oper_time) values(?,?,?,?,?,?)";
		log.debug(sql);
		try {
			PreparedStatement ps = null;
			ps = conn.prepareStatement(sql);
			String lid = IdUtil.getInstanse().getUID();
			String time = DateUtil.formatDate(new Date(), "yyyy/MM/dd HH:mm:ss SSS");
			ps.setString(1, lid);
			ps.setString(2, logInfo.getOwner());
			ps.setString(3, logInfo.getGroupName());
			ps.setString(4, logInfo.getOperateName());
			ps.setString(5, logInfo.getOperateDesc());
			ps.setString(6, time);
			int result = ps.executeUpdate();
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}

	}

	public JSONArray getLogInfoByOwnerGroup(String owner, String groupName, PageBean pageBean) {
		Connection conn = JdbcUtil.getAdminConnection();
		int start = pageBean.getStart();
		int rows = pageBean.getRows();
		String sql = rows == 0 ?  queryByOwnerGroupSql(owner, groupName) :queryByOwnerGroupSql(owner, groupName, start, rows);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet resultSet = pstmt.executeQuery();
			return JsonUtil.formatRsToJsonArray(resultSet);
		} catch (Exception e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	public int getLogInfoCountByOwnerGroup(String owner, String groupName) throws SystemException {
		Connection conn = JdbcUtil.getAdminConnection();
		String sql = countSqlByOwnerGroup(owner, groupName);
		log.debug(sql);
		try {
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				return rs.getInt("count");
			} else {
				return 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(e);
		} finally {
			try {
				JdbcUtil.closeConnection(conn);
			} catch (Exception e) {
				e.printStackTrace();
				throw new SystemException(e);
			}
		}
	}

	private String countSqlByOwnerGroup(String owner, String groupName) {
		StringBuffer sb = new StringBuffer(
				"select count(*) count from  optim.modudf_log m where m.owner='"
						+ owner + "' and m.group_name='" + groupName + "'");
		return sb.toString();
	}

	private String queryByOwnerGroupSql(String owner, String groupName) {
		StringBuffer sb = new StringBuffer(
				"select t.* from optim.modudf_log t where  t.owner='"
						+ owner + "' and t.group_name='" + groupName + "' order by t.oper_time desc");
		return sb.toString();
	}

	private String queryByOwnerGroupSql(String owner, String groupName, int start, int rows) {
		StringBuffer sb = new StringBuffer("(" + this.queryByOwnerGroupSql(owner, groupName) + ") ");
		sb.append("limit " + start + "," + rows);
		return sb.toString();
	}

	public static void main(String[] args) {
		LogInfo l = new LogInfo();
		l.setLogId(IdUtil.getInstanse().getUID());
		l.setGroupName("group1");
		l.setOperateDesc("sadf");
		l.setOperateName("开始脱敏表中的数据...");
		l.setOperateTime("sfaf");
		l.setOwner("aa");
		new LogInfoDao().addLogInfo(l);
		
	}
}
